<?php if(! defined('HAS_POWER') ) exit('无权访问!');
/**
==============================================================================
 * Class Controller GracePHP的内置模型基类
 * @author 肖永标
 * @date 2014-12-14
==============================================================================
 */
abstract class Model {

    /**
     * @var null|PDO $db 数据库链接实例
     */
    private $db = null;
    
    /**
     * @var string $table 当前表名
     */
    private $table = '';
    
    /**
     * @var string $sql 当前sql语句
     */
    private $sql = '';
    
    /**
     * @var string $where sql的where子句
     */
    private $where = '';

    /**
     * @var array $params sql的绑定参数
     */
    private $params = array();

    /**
     * @var string $fields sql的筛选字段
     */
    private $fields = '*';

    /**
     * @var string $limit sql的limit子句
     */
    private $limit = '';

    /**
     * @var string $order sql的order by子句
     */
    private $order = '';
    
    /**
     * @var string $group sql的group by子句
     */
    private $group = '';

    /**
     * @var string $alias sql的表别名
     */
    private $alias = '';

    /**
     * @var string $join 表连接
     */
    private $join = '';

    /**
     * 构造函数 获取DB实例
     */
    public function __construct()
    {
        $this->db = DB::getInstance();
        $this->table = Config::getItem('db.tablePrefix') . $this->table();
    }


    /** 构造where子句
     * @param string $where where子句
     * @param array $params 绑定参数
     * @return $this
     */
    public function where($where, $params=array())
    {
        $this->where = ' where ' . $where;
        $this->params = $params;
        return $this;
    }


    /** 构造筛选字段
     * @param string $fields 需要筛选的字段
     * @return $this
     */
    public function field($fields)
    {
        $this->fields = $fields;
        return $this;
    }


    /** 构造limit子句
     * @param string $arg1 参数1 可以是偏移量或者限制数
     * @param string $arg2 参数2 可以是偏移量或者限制数
     * @return $this
     */
    public function limit($arg1, $arg2='')
    {
        if(empty($arg2))
            $this->limit = ' limit ' . $arg1;
        else
            $this->limit = ' limit ' . $arg1 . ',' . $arg2;

        return $this;
    }


    /** 构造order子句
     * @param string $order order子句
     * @return $this
     */
    public function order($order)
    {
        $this->order = ' order by ' . $order;
        return $this;
    }

    public function group($group)
    {
        $this->group = $group;
        return $this;
    }


    /** 获取特定的筛选语句
     * @return array
     */
    public function select()
    {
        $this->_safeParse();
        $this->sql = "select {$this->fields} from `{$this->table}` {$this->alias} {$this->join} {$this->where} {$this->group} {$this->order} {$this->limit}";
        return $this->_getData($this->sql);
    }


    /** 获取单条特定的筛选语句
     * @return array
     */
    public function find()
    {
        $this->_safeParse();
        $this->sql = "select {$this->fields} from `{$this->table}` {$this->alias} {$this->join} {$this->where} {$this->group} {$this->order} limit 1";
        $data = $this->_getData($this->sql);
        return $data[0];
    }


    public function query($sql)
    {
        return $this->_getData($sql);
    }

    /** 统计数据
     * @param string $field 需要统计的字段
     * @return mixed
     */
    public function count($field='*')
    {
        $this->sql = "select count($field) as count from `{$this->table}` {$this->where}";
        $this->_safeParse();

        $result = $this->_getData($this->sql);

        return $result[0]['count'];
    }


    /** 筛选最大值
     * @param string $field 需要计算的字段
     * @return mixed
     */
    public function max($field)
    {
        $this->sql = "select max($field) as max from `{$this->table}` {$this->where}";
        $this->_safeParse();

        $result = $this->_getData($this->sql);

        return $result[0]['max'];
    }


    /** 筛选最小值
     * @param string $field 需要计算的字段
     * @return mixed
     */
    public function min($field)
    {
        $this->sql = "select min($field) as min from `{$this->table}` {$this->where}";
        $this->_safeParse();

        $result = $this->_getData($this->sql);

        return $result[0]['min'];
    }


    /** 筛选平均值
     * @param string $field 需要计算的字段
     * @return mixed
     */
    public function avg($field)
    {
        $this->sql = "select avg($field) as avg from `{$this->table}` {$this->where}";
        $this->_safeParse();

        $result = $this->_getData($this->sql);

        return $result[0]['avg'];
    }


    /** 统计字段值的总数
     * @param string $field 需要统计的字段
     * @return mixed
     */
    public function sum($field)
    {
        $this->sql = "select sum($field) as sum from `{$this->table}` {$this->where}";
        $this->_safeParse();

        $result = $this->_getData($this->sql);

        return $result[0]['sum'];
    }


    /** 插入数据
     * @param array $data 键值对 对应 字段 -> 值
     * @return int
     */
    public function insert($data)
    {
        $this->sql = "insert into `{$this->table}` (";

        foreach($data as $key=>$val)
        {
            $this->sql .= "`$key`,";
        }

        $this->sql = substr($this->sql, 0, -1);
        $this->sql .= ')';

        $this->sql .= ' values(';

        foreach($data as $key=>$val)
        {
            $this->sql .= "'" . $val . "',";
        }

        $this->sql = substr($this->sql, 0, -1);
        $this->sql .= ')';

        $stmt = $this->db->prepare($this->sql);
        $stmt->execute();

        return $stmt->rowCount();
    }


    /** 更新数据
     * @param array $data 键值对 对应 字段 -> 值
     * @return int
     * @throws Exception
     */
    public function update($data)
    {
        $this->sql = "update `{$this->table}` set ";

        foreach($data as $key=>$val)
        {
            if((strpos($key, '+') !== false) || (strpos($key, '-') !== false))
            {
                $oper = substr($key, 0, 1);
                $key = substr($key, 1);
                $this->sql .= "$key = $key$oper$val,";
            }
            else
            {
                $this->sql .= "$key = '$val',";
            }
        }

        $this->sql = substr($this->sql, 0, -1);

        if(!$this->where)
            throw new Exception('请先确认更新的条件!');

        $this->sql .= $this->where;

        $stmt = $this->db->prepare($this->sql);
        $stmt->execute();

        return $stmt->rowCount();
    }


    /** 删除数据
     * @return int
     * @throws Exception
     */
    public function delete()
    {
        if(!$this->where)
            throw new Exception('清先确认删除的条件!');

        $this->sql = "delete from `{$this->table}` {$this->where}";
        $this->_safeParse();

        $stmt = $this->db->prepare($this->sql);
        $stmt->execute();

        return $stmt->rowCount();
    }


    /** 设置表别名
     * @param string $as 表别名
     * @return $this
     */
    public function alias($as)
    {
        $this->alias = 'as ' . $as;

        return $this;
    }


    /** 外表链接
     * @param array $joinArr 链接数组
     * @return $this
     */
    public function join($joinArr)
    {
        $str = '';
        foreach($joinArr as $k=>$v)
        {
            $temp = explode(' ', $v[0]);
            $table = '`' . $temp[0] . '` ' . implode(' ', array_slice($temp, 1, sizeof($temp)));
            $str .= ' left join ' . $table . ' on ' . $v[1];
        }
        $this->join = $str;

        return $this;
    }

    /** 获取表字段
     * @return array
     */
    public function getFields()
    {
        $this->sql = "select COLUMN_NAME from information_schema.columns where table_name='{$this->table}'";
        $rs = $this->_getData($this->sql);
        $fields = array();

        foreach($rs as $k=>$v)
        {
            $fields[] = $v['COLUMN_NAME'];
        }

        return $fields;
    }


    /** 返回上一条sql语句，用于调试
     * @return string
     */
    public function getLastSql()
    {
        $i = 0;

        while(($pos = strpos($this->sql, '?')) !== false)
        {
            $sql = substr($this->sql, 0, $pos + 1);
            $temp = substr($this->sql, $pos + 1);

            $param = $this->params[$i];
            $param = "'" . $this->params[$i] . "'";
            $this->sql = str_replace('?', $param, $sql);

            $this->sql = $this->sql . $temp;
            $i++;
        }

        return $this->sql;
    }


    /** 获取sql筛选回来的数据
     * @param string $sql sql语句
     * @return array
     */
    private function _getData($sql)
    {
        $stmt = $this->db->prepare($sql);
        $stmt->execute($this->params);

        $result = $stmt->fetchAll();

        $keys = array_keys($result);

        foreach($result as $k=>&$v)
        {
            $keys = array_keys($v);
            foreach($keys as $k2=>&$v2)
            {
                if(is_int($v2))
                    unset($v[$v2]);
            }
        }

        return $result; 
    }


    /**
     * 安全地位字段加上保护标识符
     */
    private function _safeParse()
    {
        if($this->fields != '*')
        {
            $result = explode(',', $this->fields);

            foreach ($result as $k => $v)
            {
                $field = $v;
                if (strpos($v, '.') !== false)
                {
                    $temp = explode('.', $v);
                    $field = $temp[1];
                }

                $this->fields = str_replace($field, "`" . $field . "`", $this->fields);
            }
        }
    }


    /** 返回当前模型的表名
     * @return mixed
     */
    abstract public function table();
}